﻿using System;
using System.Configuration;
using System.Collections.Specialized;
using System.Configuration.Provider;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text;
using System.Web.Configuration;
using System.Web.Security;
using System.Diagnostics;

// provider 基于以下Users表结构建立.
// 
//CREATE TABLE [dbo].[Users](
//	[UserID] [uniqueidentifier] NOT NULL, 
//	[Username] [nvarchar](255) NOT NULL,
//	[ApplicationName] [nvarchar](255) NOT NULL,
//	[Email] [nvarchar](128) NOT NULL,
//	[Comment] [nvarchar](255) NULL,
//	[Password] [nvarchar](128) NOT NULL,
//	[PasswordQuestion] [nvarchar](255) NULL,
//	[PasswordAnswer] [nvarchar](255) NULL,
//	[IsApproved] [bit] NULL,
//	[LastActivityDate] [datetime] NULL,
//	[LastLoginDate] [datetime] NULL,
//	[LastPasswordChangedDate] [datetime] NULL,
//	[CreationDate] [datetime] NULL,
//	[IsOnLine] [bit] NULL,
//	[IsLockedOut] [bit] NULL,
//	[LastLockedOutDate] [datetime] NULL,
//	[FailedPasswordAttemptCount] [int] NULL,
//	[FailedPasswordAttemptWindowStart] [datetime] NULL,
//	[FailedPasswordAnswerAttemptCount] [int] NULL,
//	[FailedPasswordAnswerAttemptWindowStart] [datetime] NULL,
//PRIMARY KEY CLUSTERED 
//(
//	[UserID] ASC
//)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
//) ON [PRIMARY]
// 

namespace Auth
{
    public sealed class myMembershipProvider : MembershipProvider
    {

        #region 类变量

        private int newPasswordLength = 8;//新密码长度，默认
        private string connectionString; //连接字符串
        private string applicationName;//应用程序名
        private bool enablePasswordReset; //允许密码重置
        private bool enablePasswordRetrieval; //允许密码取回
        private bool requiresQuestionAndAnswer; //必须填写问题与答案
        private bool requiresUniqueEmail;//电子邮件必须唯一
        private int maxInvalidPasswordAttempts;//密码最多尝试次数
        private int passwordAttemptWindow;//密码尝试窗口？？
        private MembershipPasswordFormat passwordFormat; //密码加密方式，枚举类型
        private int minRequiredNonAlphanumericCharacters; //密码中最少的非阿拉伯符号个数
        private int minRequiredPasswordLength;//密码最短位数
        private string passwordStrengthRegularExpression; //密码强度正则表达式
        private MachineKeySection machineKey; //机器码，在加密时使用.
        //异常处理
        private string eventSource = "myMembershipProvider"; //时间来源
        private string eventLog = "Application";
        private string exceptionMessage = "出现异常，请检查Event Log.";
        //false时异常提交给调用者，true时写入日志Event Log
        private bool writeExceptionsToEventLog = false;

        #endregion

        #region 枚举类型

        private enum FailureType
        {
            Password = 1,
            PasswordAnswer = 2
        }

        #endregion

        #region 属性设置
        public bool WriteExceptionsToEventLog
        {
            get { return writeExceptionsToEventLog; }
            set { writeExceptionsToEventLog = value; }
        }

        public override string ApplicationName
        {
            get
            {
                return applicationName;
            }
            set
            {
                applicationName = value;
            }
        }

        public override bool EnablePasswordReset
        {
            get
            {
                return enablePasswordReset;
            }
        }

        public override bool EnablePasswordRetrieval
        {
            get
            {
                return enablePasswordRetrieval;
            }
        }

        public override bool RequiresQuestionAndAnswer
        {
            get
            {
                return requiresQuestionAndAnswer;
            }
        }

        public override bool RequiresUniqueEmail
        {
            get
            {
                return requiresUniqueEmail;
            }
        }

        public override int MaxInvalidPasswordAttempts
        {
            get
            {
                return maxInvalidPasswordAttempts;
            }
        }

        public override int PasswordAttemptWindow
        {
            get
            {
                return passwordAttemptWindow;
            }
        }

        public override MembershipPasswordFormat PasswordFormat
        {
            get
            {
                return passwordFormat;
            }
        }

        public override int MinRequiredNonAlphanumericCharacters
        {
            get
            {
                return minRequiredNonAlphanumericCharacters;
            }
        }

        public override int MinRequiredPasswordLength
        {
            get
            {
                return minRequiredPasswordLength;
            }
        }

        public override string PasswordStrengthRegularExpression
        {
            get
            {
                return passwordStrengthRegularExpression;
            }
        }

        #endregion

        #region 初始化

        public override void Initialize(string name, NameValueCollection config)
        {

            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            if (name == null || name.Length == 0)
            {
                name = "myMembershipProvider";
            }

            if (String.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "myMembershipProvider应用");
            }

            //初始化基类
            base.Initialize(name, config);

            //初始化类属性
            applicationName = GetConfigValue(config["applicationName"], System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            maxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
            passwordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
            minRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredAlphaNumericCharacters"], "1"));
            minRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
            passwordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], String.Empty));
            enablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
            enablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
            requiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
            requiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));

            if (GetConfigValue(config["writeExceptionsToEventLog"], "FALSE") == "TRUE")
            {
                writeExceptionsToEventLog = true;
            }
            string temp_format = config["passwordFormat"];
            if (temp_format == null)
            {
                temp_format = "Hashed";
            }

            switch (temp_format)
            {
                case "Hashed":
                    passwordFormat = MembershipPasswordFormat.Hashed;
                    break;
                case "Encrypted":
                    passwordFormat = MembershipPasswordFormat.Encrypted;
                    break;
                case "Clear":
                    passwordFormat = MembershipPasswordFormat.Clear;
                    break;
                default:
                    throw new ProviderException("Web.config的密码加密方式不支持");
            }

            ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if ((ConnectionStringSettings == null) || (ConnectionStringSettings.ConnectionString.Trim() == String.Empty))
            {
                throw new ProviderException("连接字符串为空");
            }

            connectionString = ConnectionStringSettings.ConnectionString;

            //从配置信息中读取加密解密密钥信息.
            System.Configuration.Configuration cfg = WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            machineKey = cfg.GetSection("system.web/machineKey") as MachineKeySection;

            if (machineKey.ValidationKey.Contains("AutoGenerate"))
            {
                if (PasswordFormat != MembershipPasswordFormat.Clear)
                {
                    throw new ProviderException("Hashed 或 Encrypted 密码不支持自生成密钥.");
                }
            }
        }

        private string GetConfigValue(string configValue, string defaultValue)
        {
            if (String.IsNullOrEmpty(configValue))
            {
                return defaultValue;
            }

            return configValue;
        }

        #endregion

        #region 实现 MembershipProvider 的主要方法

        /// <summary>
        /// 更改密码
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="oldPwd">旧密码</param>
        /// <param name="newPwd">新密码</param>
        /// <returns>T/F .</returns>
        public override bool ChangePassword(string username, string oldPwd, string newPwd)
        {

            if (!ValidateUser(username, oldPwd))
            {
                return false;
            }

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPwd, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                if (args.FailureInformation != null)
                {
                    throw args.FailureInformation;
                }
                else
                {
                    throw new Exception("Change password canceled due to new password validation failure.");
                }
            }

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_ChangePassword", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@password", SqlDbType.NVarChar, 255).Value = EncodePassword(newPwd);
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ChangePassword");
                }
                else
                {
                    throw e;
                }
                return false;
            }
            finally
            {
                sqlConnection.Close();
            }

            return true;

        }

        /// <summary>
        /// 改变密码保护的问题与答案.
        /// </summary>
        /// <param name="username">用户名.</param>
        /// <param name="password">密码.</param>
        /// <param name="newPwdQuestion">新密码问题.</param>
        /// <param name="newPwdAnswer">新密码答案.</param>
        /// <returns>T/F 更改.</returns>
        public override bool ChangePasswordQuestionAndAnswer(
          string username,
          string password,
          string newPwdQuestion,
          string newPwdAnswer)
        {
            //验证用户
            if (!ValidateUser(username, password))
            {
                return false;
            }

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_ChangePasswordQuestionAnswer", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@returnValue", SqlDbType.Int, 0).Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add("@question", SqlDbType.NVarChar, 255).Value = newPwdQuestion;
            sqlCommand.Parameters.Add("@answer", SqlDbType.NVarChar, 255).Value = EncodePassword(newPwdAnswer);
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username; ;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                if ((int)sqlCommand.Parameters["@returnValue"].Value != 0)
                {
                    return false;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ChangePasswordQuestionAndAnswer");
                }
                else
                {
                    throw e;
                }

                return false;
            }
            finally
            {
                sqlConnection.Close();
            }

            return true;

        }
        /// <summary>
        /// 创建新用户
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="password">密码</param>
        /// <param name="email">邮件地址</param>
        /// <param name="passwordQuestion">密保问题.</param>
        /// <param name="passwordAnswer">密保答案.</param>
        /// <param name="isApproved">启用</param>
        /// <param name="userID">用户ID</param>
        /// <param name="status">状态</param>
        /// <returns>MembershipUser</returns>

        public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
        {
            
            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);
            //验证密码可用性
            OnValidatingPassword(args);

            if (args.Cancel)
            {
                status = MembershipCreateStatus.InvalidPassword;
                throw new ArgumentException("密码格式无效");

            }
            //邮件地址唯一且已存在
            if ((RequiresUniqueEmail && (GetUserNameByEmail(email) != String.Empty)))
            {
                status = MembershipCreateStatus.DuplicateEmail;
                throw new ProviderException("当前设置邮件不允许重复");
            }
            //尝试获取以用户名获取用户
            MembershipUser membershipUser = GetUser(username, false);

            passwordQuestion = GetConfigValue(passwordQuestion, "未设定");
            passwordAnswer = GetConfigValue(passwordAnswer, "无");

            if (membershipUser == null)
            {
                System.DateTime createDate = DateTime.Now;
                //调用存储过程，建立用户
                SqlConnection sqlConnection = new SqlConnection(connectionString);
                SqlCommand sqlCommand = new SqlCommand("User_Ins", sqlConnection);

                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.Add("@returnValue", SqlDbType.Int, 0).Direction = ParameterDirection.ReturnValue;
                sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username; ;
                sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;
                sqlCommand.Parameters.Add("@password", SqlDbType.NVarChar, 255).Value = EncodePassword(password);
                sqlCommand.Parameters.Add("@email", SqlDbType.NVarChar, 128).Value = email;
                sqlCommand.Parameters.Add("@passwordQuestion", SqlDbType.NVarChar, 255).Value = passwordQuestion;
                sqlCommand.Parameters.Add("@passwordAnswer", SqlDbType.NVarChar, 255).Value = EncodePassword(passwordAnswer);
                sqlCommand.Parameters.Add("@isApproved", SqlDbType.Bit).Value = isApproved;
                sqlCommand.Parameters.Add("@comment", SqlDbType.NVarChar, 255).Value = String.Empty;

                try
                {
                    sqlConnection.Open();

                    sqlCommand.ExecuteNonQuery();
                    if ((int)sqlCommand.Parameters["@returnValue"].Value == 0)
                    {

                        status = MembershipCreateStatus.Success;
                    }
                    else
                    {
                        status = MembershipCreateStatus.UserRejected;
                    }
                }
                catch (SqlException e)
                {
                    //Add exception handling here.
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(e, "CreateUser");
                    }
                    else
                    {
                        throw e;
                    }
                    status = MembershipCreateStatus.ProviderError;
                }
                finally
                {
                    sqlConnection.Close();
                }

                return GetUser(username, false);
            }
            else
            {
                status = MembershipCreateStatus.DuplicateUserName;
                throw new ProviderException("用户" + username + "已存在");
            }
        }
        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="username">删除用户</param>
        /// <param name="deleteAllRelatedData">是否删除所有相关信息</param>
        /// <returns>T/F 如果成功删除</returns>
        public override bool DeleteUser(
         string username,
         bool deleteAllRelatedData
        )
        {
            if (GetUser(username, false) == null)
            {
                throw new ProviderException("用户" + username + "不存在.");
            }

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Del", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@returnValue", SqlDbType.Int, 0).Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username; ;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                if ((int)sqlCommand.Parameters["@returnValue"].Value == 0)
                {
                    if (deleteAllRelatedData)
                    {

                        //这里需要执行删除所有数据库中相关信息的操作

                    }
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "DeleteUser");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                sqlConnection.Close();
            }

            return true;

        }
        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="totalRecords">记录总计</param>
        /// <returns>用户集合.</returns>

        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Users_Sel", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            MembershipUserCollection users = new MembershipUserCollection();

            SqlDataReader sqlDataReader = null;
            totalRecords = 0;

            try
            {
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;
                //从头开始读取，当读到开始标记时，开始添加用户，到结束标记时，终止读取过程。
                while (sqlDataReader.Read())
                {
                    if (counter >= startIndex)
                    {
                        users.Add(GetUserFromReader(sqlDataReader));
                    }

                    if (counter >= endIndex) { sqlCommand.Cancel(); }
                    counter += 1;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetAllUsers");
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
            }

            return users;

        }
        /// <summary>
        /// 获取当前在线用户数
        /// </summary>
        /// <returns>  /// # 在线用户数.</returns>
        public override int GetNumberOfUsersOnline()
        {
            //根据membership中定义的userIsOnlineTimeWindow设定的时间判断是否在线
            TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
            //从现在减掉N分钟，大于这个时间的都为在线
            DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Users_NumberOnline", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;
            sqlCommand.Parameters.Add("@compareDate", SqlDbType.DateTime).Value = compareTime;

            int numOnline = 0;

            try
            {
                sqlConnection.Open();

                numOnline = Convert.ToInt32(sqlCommand.ExecuteScalar());
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetNumberOfUsersOnline");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                sqlConnection.Close();
            }

            return numOnline;

        }
        /// <summary>
        ///获取某用户的密码.
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="answer">密保答案</param>
        /// <returns>用户的密码.</returns>
        public override string GetPassword(
         string username,
         string answer
        )
        {

            if (!EnablePasswordRetrieval)
            {
                throw new ProviderException("密码无法取回.");
            }
            //如果为Hashed加密方式，也无法获得原始密码
            if (PasswordFormat == MembershipPasswordFormat.Hashed)
            {
                throw new ProviderException("无法获取Hashed加密方式的密码.");
            }

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_GetPassword", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            string password = String.Empty;
            string passwordAnswer = String.Empty;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow & CommandBehavior.CloseConnection);
                //如果有数据
                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    //查看数据的第三行
                    if (sqlDataReader.GetBoolean(2))
                    {
                        throw new MembershipPasswordException("用户被锁定.");
                    }

                    password = sqlDataReader.GetString(0);
                    passwordAnswer = sqlDataReader.GetString(1);
                }
                else
                {
                    throw new MembershipPasswordException("没有找到用户");
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetPassword");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }
            //如果保存了密保问题，检查密保问题答案是否正确
            if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
            {
                //密保答案错误
                UpdateFailureCount(username, FailureType.PasswordAnswer);

                throw new MembershipPasswordException("密保答案错误.");
            }
            //如果密码是Encrypted加密的，解密查询到的密码
            if (PasswordFormat == MembershipPasswordFormat.Encrypted)
            {
                password = UnEncodePassword(password);
            }

            return password;
        }
        /// <summary>
        /// 获取用户
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="userIsOnline">T/F是否更新在线状态</param>
        /// <returns></returns>
        public override MembershipUser GetUser(
        string username,
         bool userIsOnline
        )
        {

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Sel", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            MembershipUser membershipUser = null;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader();

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    membershipUser = GetUserFromReader(sqlDataReader);
                    //如果设置为需要更新
                    if (userIsOnline)
                    {
                        sqlDataReader.Close();
                        SqlCommand sqlUpdateCommand = new SqlCommand("User_UpdateActivityDate_ByUserName", sqlConnection);
                        sqlUpdateCommand.CommandType = CommandType.StoredProcedure;
                        sqlUpdateCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
                        sqlUpdateCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;
                        sqlUpdateCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUser_By_UserName");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            return membershipUser;
        }
        /// <summary>
        /// 根据用户ID获取用户信息.
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="userIsOnline">T/F 如果用户在线，更新活动信息.</param>
        /// <returns></returns>
        public override MembershipUser GetUser(object userID, bool userIsOnline)
        {

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_SelByUserID", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@userID", SqlDbType.UniqueIdentifier).Value = userID;

            MembershipUser membershipUser = null;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    membershipUser = GetUserFromReader(sqlDataReader);
                    //在线的话根据用户ID更新最后活动时间
                    if (userIsOnline)
                    {
                        SqlCommand sqlUpdateCommand = new SqlCommand("User_UpdateActivityDate_ByUserID", sqlConnection);

                        sqlUpdateCommand.CommandType = CommandType.StoredProcedure;
                        sqlUpdateCommand.Parameters.Add("@userID", SqlDbType.NVarChar, 255).Value = userID;
                        sqlUpdateCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;
                        sqlUpdateCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUser_By_ID");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            return membershipUser;

        }

        /// <summary>
        /// 开锁用户
        /// </summary>
        /// <param name="username">用户名</param>
        /// <returns>T/F 如果锁定了</returns>
        public override bool UnlockUser(
         string username
        )
        {

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Unlock", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@returnValue", SqlDbType.Int, 0).Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            try
            {
                sqlConnection.Open();

                sqlCommand.ExecuteNonQuery();
                if ((int)sqlCommand.Parameters["@returnValue"].Value == 0)
                {
                    return false;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "UnLockUser");
                }
                else
                {
                    throw e;
                }

                return false;
            }
            finally
            {
                sqlConnection.Close();
            }

            return true;

        }

        /// <summary>
        /// 根据邮件地址获取用户名
        /// </summary>
        /// <param name="email">邮件地址</param>
        /// <returns>用户名</returns>
        public override string GetUserNameByEmail(string email)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("UserName_Sel_ByEmail", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@email", SqlDbType.NVarChar, 128).Value = email;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            string username = String.Empty;

            try
            {
                sqlConnection.Open();
                username = Convert.ToString(sqlCommand.ExecuteScalar());
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUserNameByEmail");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                sqlConnection.Close();
            }

            if (username == null)
            {
                return String.Empty;
            }
            else
            {
                username.Trim();//前后空字符串
            }

            return username;
        }
        /// <summary>
        /// 重置用户密码.
        /// </summary>
        /// <param name="username">用户名.</param>
        /// <param name="answer">密保答案</param>
        /// <returns></returns>
        public override string ResetPassword(string username, string answer)
        {

            if (!EnablePasswordReset)
            {
                throw new NotSupportedException("密码不允许重置.");
            }
            /*
            if ((answer == null) && (RequiresQuestionAndAnswer))
            {
                UpdateFailureCount(username, FailureType.PasswordAnswer);

                throw new ProviderException("密码重置必须填写密保答案.");
            }
             * */
            //生成一个随机密码
            string newPassword = System.Web.Security.Membership.GeneratePassword(
              newPasswordLength,
              MinRequiredNonAlphanumericCharacters
              );
            //检验新密码有效性
            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                if (args.FailureInformation != null)
                {
                    throw args.FailureInformation;
                }
                else
                {
                    throw new MembershipPasswordException("新密码生成失败，无法重置.");
                }
            }

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_GetPasswordAnswer", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            int rowsAffected = 0;
            string passwordAnswer = String.Empty;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow & CommandBehavior.CloseConnection);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();

                    if (sqlDataReader.GetBoolean(1))
                    {
                        throw new MembershipPasswordException("用户被锁定.");
                    }

                    passwordAnswer = sqlDataReader.GetString(0);
                }
                else
                {
                    throw new MembershipPasswordException("用户不存在.");
                }
                /*
                if (RequiresQuestionAndAnswer && (!CheckPassword(answer, passwordAnswer)))
                {
                    UpdateFailureCount(username, FailureType.PasswordAnswer);

                    throw new MembershipPasswordException("错误的密保问题.");
                }
                */
                sqlDataReader.Close();
                SqlCommand sqlUpdateCommand = new SqlCommand("User_UpdatePassword", sqlConnection);

                sqlUpdateCommand.CommandType = CommandType.StoredProcedure;
                //加密后更新
                sqlUpdateCommand.Parameters.Add("@password", SqlDbType.NVarChar, 255).Value = EncodePassword(newPassword);
                sqlUpdateCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
                sqlUpdateCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;
                rowsAffected = sqlUpdateCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ResetPassword");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            if (rowsAffected > 0)
            {
                return newPassword;
            }
            else
            {
                throw new MembershipPasswordException("用户未找到或已被锁定，无法重置.");
            }
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="_membershipUser">包含信息的MembershipUser对象.</param>
        public override void UpdateUser(MembershipUser membershipUser)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Upd", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@email", SqlDbType.NVarChar, 128).Value = membershipUser.Email;
            sqlCommand.Parameters.Add("@comment", SqlDbType.NVarChar, 255).Value = membershipUser.Comment;
            sqlCommand.Parameters.Add("@isApproved", SqlDbType.Bit).Value = membershipUser.IsApproved;
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = membershipUser.UserName;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "UpdateUser");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// 根据用户名密码验证
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>T/F 是否正确.</returns>
        public override bool ValidateUser(string username, string password)
        {

            bool isValid = false;

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_Validate", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            SqlDataReader sqlDataReader = null;
            bool isApproved = false;
            string storedPassword = String.Empty;

            try
            {
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();
                    storedPassword = sqlDataReader.GetString(0);
                    isApproved = sqlDataReader.GetBoolean(1);
                }
                else
                {
                    return false;
                }

                sqlDataReader.Close();

                if (CheckPassword(password, storedPassword))
                {
                    if (isApproved)
                    {
                        isValid = true;

                        SqlCommand sqlUpdateCommand = new SqlCommand("User_UpdateLoginDate", sqlConnection);

                        sqlUpdateCommand.CommandType = CommandType.StoredProcedure;
                        sqlUpdateCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
                        sqlUpdateCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;
                        sqlUpdateCommand.ExecuteNonQuery();
                    }
                }
                else
                {
                    sqlConnection.Close();
                    UpdateFailureCount(username, FailureType.Password);
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ValidateUser");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
                if ((sqlConnection != null) && (sqlConnection.State == ConnectionState.Open)) { sqlConnection.Close(); }
            }

            return isValid;
        }
        /// <summary>
        /// 查找所有符合条件的用户
        /// </summary>
        /// <param name="usernameToMatch">查找用户名.</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="totalRecords">总记录数.</param>
        /// <returns>MembershipUser对象集合.</returns>

        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Users_Sel_ByUserName", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = usernameToMatch;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            MembershipUserCollection membershipUsers = new MembershipUserCollection();
            SqlDataReader sqlDataReader = null;
            int counter = 0;

            try
            {
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (sqlDataReader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser membershipUser = GetUserFromReader(sqlDataReader);
                        membershipUsers.Add(membershipUser);
                    }

                    if (counter >= endIndex) { sqlCommand.Cancel(); }

                    counter += 1;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "FindUsersByName");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            totalRecords = counter;

            return membershipUsers;
        }

        /// <summary>
        ///根据邮件地址查找所有符合条件的用户
        /// </summary>
        /// <param name="emailToMatch">检索的邮件地址</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="totalRecords">总记录数.</param>
        /// <returns>MembershipUser对象集合.</returns>

        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("Users_Sel_ByUserName", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@email", SqlDbType.NVarChar, 255).Value = emailToMatch;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            MembershipUserCollection membershipUsers = new MembershipUserCollection();
            SqlDataReader sqlDataReader = null;
            int counter = 0;

            try
            {
                sqlConnection.Open();
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (sqlDataReader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser membershipUser = GetUserFromReader(sqlDataReader);
                        membershipUsers.Add(membershipUser);
                    }

                    if (counter >= endIndex) { sqlCommand.Cancel(); }

                    counter += 1;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "FindUsersByEmail");
                }
                else
                {
                    throw e;
                }

            }
            finally
            {
                if (sqlDataReader != null) { sqlDataReader.Close(); }
            }

            totalRecords = counter;

            return membershipUsers;
        }

        #endregion

        #region 实用私有函数
        /// <summary>
        /// 从一个记录集中创建MembershipUser对象.数据集中必须0：用户ID，1：用户名,2:邮件地址 
        /// 后面为可变 3、密保问题，4、备注 5、允许6、锁定7、创建时间8、上次登录9、上次活动10、上次密码更改11、上次锁定时间
        /// </summary>
        /// <param name="sqlDataReader">数据集.</param>
        /// <returns>MembershipUser对象.</returns>
        private MembershipUser GetUserFromReader(SqlDataReader sqlDataReader)
        {

            object userID = sqlDataReader.GetValue(0);
            string username = sqlDataReader.GetString(1);
            string email = sqlDataReader.GetString(2);

            string passwordQuestion = String.Empty;
            if (sqlDataReader.GetValue(3) != DBNull.Value)
            {
                passwordQuestion = sqlDataReader.GetString(3);
            }

            string comment = String.Empty;
            if (sqlDataReader.GetValue(4) != DBNull.Value)
            {
                comment = sqlDataReader.GetString(4);
            }

            bool isApproved = sqlDataReader.GetBoolean(5);
            bool isLockedOut = sqlDataReader.GetBoolean(6);
            DateTime creationDate = sqlDataReader.GetDateTime(7);

            DateTime lastLoginDate = new DateTime();
            if (sqlDataReader.GetValue(8) != DBNull.Value)
            {
                lastLoginDate = sqlDataReader.GetDateTime(8);
            }

            DateTime lastActivityDate = sqlDataReader.GetDateTime(9);
            DateTime lastPasswordChangedDate = sqlDataReader.GetDateTime(10);

            DateTime lastLockedOutDate = new DateTime();
            if (sqlDataReader.GetValue(11) != DBNull.Value)
            {
                lastLockedOutDate = sqlDataReader.GetDateTime(11);
            }

            MembershipUser membershipUser = new MembershipUser(
                this.Name,
                username,
                userID,
                email,
                passwordQuestion,
                comment,
                isApproved,
                isLockedOut,
                creationDate,
                lastLoginDate,
                lastActivityDate,
                lastPasswordChangedDate,
                lastLockedOutDate
              );

            return membershipUser;

        }

        /// <summary>
        /// 将十六进制数转化成字节数组，用于转换配置中的加密密钥
        /// </summary>
        /// <param name="hexString">十六进制字符串</param>
        /// <returns></returns>
        private byte[] HexToByte(string hexString)
        {
            byte[] returnBytes = new byte[hexString.Length / 2];
            for (int i = 0; i < returnBytes.Length; i++)
                returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
            return returnBytes;
        }

        /// <summary>
        /// 更新密码与密保答案错误次数
        /// </summary>
        /// <param name="username">用户名.</param>
        /// <param name="failureType">错误类型</param>
        /// <remarks></remarks>
        private void UpdateFailureCount(string username, FailureType failureType)
        {

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_FailedPassword_Upd", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@failureType", SqlDbType.Int, 0).Value = failureType;
            sqlCommand.Parameters.Add("@passwordAttemptWindow", SqlDbType.Int, 0).Value = passwordAttemptWindow;
            sqlCommand.Parameters.Add("@maxInvalidPasswordAttempts", SqlDbType.Int, 0).Value = maxInvalidPasswordAttempts;

            sqlCommand.Parameters.Add("@userName", SqlDbType.NVarChar, 255).Value = username;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "AddUsersToRoles");
                }
                else
                {
                    throw e;
                }

            }

        }
        /// <summary>
        /// 根据MembershipPasswordFormat验证密码.
        /// </summary>
        /// <param name="password">用户输入的密码</param>
        /// <param name="dbpassword">数据库中的密码</param>
        /// <returns></returns>
        /// <remarks></remarks>
        private bool CheckPassword(string password, string dbpassword)
        {
            string pass1 = password;
            string pass2 = dbpassword;

            switch (PasswordFormat)
            {

                case MembershipPasswordFormat.Encrypted: //数据库中的直接解密
                    pass2 = UnEncodePassword(dbpassword);
                    break;
                case MembershipPasswordFormat.Hashed:
                    pass1 = EncodePassword(password);//将用户输入的密码密码哈希加密
                    break;
                default:
                    break;
            }
            //比较两个密码
            if (pass1 == pass2)
            {
                return true;
            }

            return false;
        }

        /// <summary>
        /// 加密密码.
        /// </summary>
        /// <param name="password">加密前密码.</param>
        /// <returns>加密后密码.</returns>
        private string EncodePassword(string password)
        {
            string encodedPassword = password;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    encodedPassword =
                      Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    HMACSHA1 hash = new HMACSHA1();
                    hash.Key = HexToByte(machineKey.ValidationKey);
                    encodedPassword =
                      Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
                    break;
                default:
                    throw new ProviderException("不支持的加密方式.");
            }

            return encodedPassword;
        }

        /// <summary>
        /// 解密.
        /// </summary>
        /// <param name="encodedPassword">解密前.</param>
        /// <returns>解密后.</returns>
        private string UnEncodePassword(string encodedPassword)
        {
            string password = encodedPassword;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    password =
                      Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    throw new ProviderException("哈希密码无法解密.");
                default:
                    throw new ProviderException("不支持的加密方式.");
            }

            return password;
        }
        /// <summary>
        /// 写入日志
        /// </summary>
        /// <param name="e">异常</param>
        /// <param name="action">操作</param>
        private void WriteToEventLog(SqlException e, string action)
        {
            EventLog log = new EventLog();
            log.Source = eventSource;
            log.Log = eventLog;

            string message = exceptionMessage + "\n\n";
            message += "Action: " + action + "\n\n";
            message += "Exception: " + e.ToString();

            log.WriteEntry(message);
        }

        #endregion

    }
}